package com.ym.excel.utils;

import com.ym.excel.jfree.YMChart;
import com.ym.excel.jfree.YMJFreeBarChart;
import com.ym.excel.jfree.YMJFreeLineChart;
import com.ym.excel.jfree.YMJFreePieChart;
import com.ym.excel.model.YMChartModel;
import com.ym.excel.model.YMExcelChartData;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.JFreeChart;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.general.DefaultPieDataset;
import org.jfree.data.general.PieDataset;

import java.io.*;
import java.util.*;

import static org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_PNG;

/**
 * Excel 读写操作类拓展，生成Excel时动态生成图表
 */
public class ExcelChartOperator extends ExcelOperator implements ExcelChartOperatorInte {

    public String writeExcelWithChart(List<List<String>> data, YMChartModel chartModel) throws Exception {
        return writeExcelWithChart("", data, chartModel);
    }

    public String writeExcelWithChart(String path, List<List<String>> data, YMChartModel chartModel) throws Exception {
        return writeExcelWithChart(path, "", data, chartModel);
    }

    public String writeExcelWithChart(String path, String fileName, List<List<String>> data, YMChartModel chartModel) throws Exception {
        return writeExcelWithChart(path, fileName, "Sheet1", data, chartModel);
    }

    public String writeExcelWithChart(String path, String fileName, String sheetName, List<List<String>> data, YMChartModel chartModel) throws Exception {
        return writeExcelWithChart(path, fileName, sheetName, null, data, chartModel);
    }

    public String writeExcelWithChart(String path, String fileName, String sheetName, List<String> title, List<List<String>> data, YMChartModel chartModel) throws Exception {
        return writeExcelWithChart(path, fileName, sheetName, title, data, null, chartModel);
    }

    public String writeExcelWithChart(String path, String fileName, String sheetName, List<String> title, List<List<String>> data, Integer[] hideColumn, YMChartModel chartModel) throws Exception {
        String excelPath = writeExcel(path, fileName, sheetName, title, data, hideColumn);
        return writeChartToExcel(excelPath, sheetName, chartModel);
    }

    public String writeChartToExcel(String excelPath, String sheetName, YMChartModel chartModel) throws Exception {
        // 生成统计图图片
        String imagePath = generateImage(excelPath, sheetName, chartModel);
        // 将统计图图片合并到Excel中
        excelPath = mergeExcelAndImage(sheetName, excelPath, imagePath, chartModel);
        // 删除图片
        deleteFile(imagePath);
        return excelPath;
    }

    /**
     * 将统计图图片合并到Excel中
     *
     * @param sheetName
     * @param excelPath
     * @param imagePath
     * @throws Exception
     */
    public String mergeExcelAndImage(String sheetName, String excelPath, String imagePath, YMChartModel chartModel) throws Exception {
        int col2 = chartModel.getxEndPosition() == null ? 8 : chartModel.getxEndPosition();
        int row2 = chartModel.getyEndPosition() == null ? 25 : chartModel.getyEndPosition();
        Workbook workbook;
        if (excelPath.endsWith(".xls")) {
            workbook = new HSSFWorkbook(new FileInputStream(excelPath));
        } else {
            workbook = new XSSFWorkbook(new FileInputStream(excelPath));
        }
        // 获取图片添加Sheet页
        Sheet sheet;
        if (sheetName == null || "".equals(sheetName.trim())) {
            sheet = workbook.getSheetAt(0);
        } else {
            sheet = workbook.getSheet(sheetName);
        }
        // 指定图片在Excel中的位置
        Drawing patriarch = sheet.createDrawingPatriarch();
        ClientAnchor anchor;
        if (patriarch instanceof HSSFPatriarch) {
            anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) col2, row2);
        } else {
            anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, col2, row2);
        }

        // 将图片转换为二进制流
        ByteArrayOutputStream bo = new ByteArrayOutputStream();

        FileInputStream fi = new FileInputStream(imagePath);
        byte[] b = new byte[1024];
        int l = -1;
        while ((l = fi.read(b)) != -1) {
            bo.write(b);
            bo.flush();
        }
        // 图便添加到Excel中
        patriarch.createPicture(anchor, workbook.addPicture(bo.toByteArray(), PICTURE_TYPE_PNG));
        // 重新输出Excel 并关闭想应的流
        FileOutputStream fo;
        try {
            fo = new FileOutputStream(excelPath);
        } catch (Exception e) { // .xls 格式兼容
            String fileSeparator = System.getProperty("file.separator");
            String newPath = excelPath.substring(0, excelPath.lastIndexOf(fileSeparator));
            String suffix = excelPath.substring(excelPath.lastIndexOf("."), excelPath.length());
            String newName = UUID.randomUUID().toString().replace("-", "").toLowerCase();
            excelPath = newPath+fileSeparator+newName+suffix;
            fo = new FileOutputStream(excelPath);
        }
        workbook.write(fo);
        try {
            bo.close();
            fi.close();
            fo.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return excelPath;
    }

    /**
     * 生成图片
     *
     * @param excelPath  excel信息
     * @param sheetName
     * @param chartModel 读取Excel信息
     * @return 图片路径信息
     */
    public String generateImage(String excelPath, String sheetName, YMChartModel chartModel) throws Exception {
        if (excelPath == null || "".equals(excelPath.trim())) {
            throw new Exception("文件路径不能为空");
        }
        File file = new File(excelPath);
        String imageFile = null;
        String separator = System.getProperty("file.separator");
        String imagePath = excelPath.substring(0, excelPath.lastIndexOf(separator)); // 获取文件存储文件夹
        try {
            Workbook workbook = WorkbookFactory.create(file);
            Sheet sheet;
            if (null == sheetName || "".endsWith(sheetName.trim())) {
                sheet = workbook.getSheetAt(0);
            } else {
                sheet = workbook.getSheet(sheetName);
            }

            // 获X轴信息
            YMExcelChartData xYMChartData = chartModel.getxInfo();
            List<String> xList = getRowData(sheet, xYMChartData, new String());

            YMChart ymChart = chartModel.getYmChart();
            // 获取分组信息
            YMExcelChartData groupYMChartData = chartModel.getGroupInfo();
            JFreeChart jFreeChart;
            if (groupYMChartData == null && ymChart instanceof YMJFreePieChart) { // 分组信息为空且绘图为饼图
                YMExcelChartData yInfo = chartModel.getyInfo();
                List<Double> dataList = getRowData(sheet, yInfo, new Double(0));
                PieDataset dataSet = generatePieDataSet(xList, dataList);
                jFreeChart = ChartFactory.createPieChart(chartModel.getTitle(), dataSet);

            } else {
                List<String> groupList = getRowData(sheet, groupYMChartData, new String());
                // 获取数值数据
                Map<String, List<Double>> listMap = new HashMap<String, List<Double>>();
                YMExcelChartData yInfo = chartModel.getyInfo();
                int rStart = yInfo.getRowStart();
                int rEnd = yInfo.getRowEnd();
                int cStart = yInfo.getRowStart();
                int cEnd = yInfo.getRowEnd();
                for (int i = 0; rStart <= rEnd; i++, rStart++) { // 按行读取数据并添加分组
                    YMExcelChartData temp = new YMExcelChartData();
                    temp.setRowStart(rStart);
                    temp.setRowEnd(rStart);
                    temp.setColStart(cStart);
                    temp.setColEnd(cEnd);
                    List<Double> list = getRowData(sheet, temp, new Double(0));
                    listMap.put(groupList.get(i), list);
                }
                // 组装DataSet
                jFreeChart = generateJFeeChart(ymChart, chartModel, xList, listMap);

            }

            imageFile = ymChart.drawChart(jFreeChart, imagePath, chartModel.getWidth(), chartModel.getHeight());

        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return imageFile;
    }

    /**
     * 生成 JFeeChart
     *
     * @param ymChart
     * @return
     */
    public JFreeChart generateJFeeChart(YMChart ymChart, YMChartModel chartModel, List<String> xList, Map<String, List<Double>> listMap) {
        JFreeChart jFreeChart = null;
        if (ymChart instanceof YMJFreeBarChart) { // 柱形图

            DefaultCategoryDataset dataSet = generateAxisDataSet(xList, listMap);
            jFreeChart = ChartFactory.createBarChart(chartModel.getTitle(), chartModel.getxDesc(), chartModel.getyDesc(), dataSet);

        } else if (ymChart instanceof YMJFreeLineChart) { // 折线图

            DefaultCategoryDataset dataSet = generateAxisDataSet(xList, listMap);
            jFreeChart = ChartFactory.createLineChart(chartModel.getTitle(), chartModel.getxDesc(), chartModel.getyDesc(), dataSet);
        } else if (ymChart instanceof YMJFreePieChart) {// 饼图单独处理

        }

        return jFreeChart;
    }

    /**
     * 组装饼图数据
     *
     * @param xData
     * @param dataList
     * @return
     */
    public PieDataset generatePieDataSet(List<String> xData, List<Double> dataList) {
        DefaultPieDataset defaultPieDataset = new DefaultPieDataset();
        for (int i = 0, len = xData.size(); i < len; i++) {
            Double d = 0d;
            try {
                d = dataList.get(i);
            } catch (Exception e) {
                e.printStackTrace();
            }
            defaultPieDataset.setValue(xData.get(i), d);
        }
        return defaultPieDataset;
    }

    /**
     * 组装 坐标轴 DataSet 数据
     *
     * @param xData
     * @param groupData
     * @return
     */
    public DefaultCategoryDataset generateAxisDataSet(List<String> xData, Map<String, List<Double>> groupData) {
        DefaultCategoryDataset categoryDataSet = new DefaultCategoryDataset();
        for (Map.Entry<String, List<Double>> entry : groupData.entrySet()) {
            String groupInfo = entry.getKey();
            List<Double> dataList = entry.getValue();
            for (int i = 0, len = dataList.size(); i < len; i++) {
                String x = "";
                try {
                    x = xData.get(i);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                categoryDataSet.addValue(dataList.get(i), groupInfo, x);
            }
        }
        return categoryDataSet;
    }

    /**
     * 读取Excel数据
     *
     * @param sheet
     * @param excelYMChartData
     * @param t
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> List<T> getRowData(Sheet sheet, YMExcelChartData excelYMChartData, T t) throws Exception {
        int rowStart = excelYMChartData.getRowStart();
        int rowEnd = excelYMChartData.getRowEnd();
        int colStart = excelYMChartData.getColStart();
        int colEnd = excelYMChartData.getColEnd();

        List<T> list = new ArrayList<T>();
        for (int i = rowStart; i <= rowEnd; i++) { // 读取行数据
            Row row = sheet.getRow(i);
            if (row == null) {
                throw new Exception("指定行为空！");
            }
            for (int j = colStart; j <= colEnd; j++) { // 读取列单元格数据
                Cell cell = row.getCell(j);
                if (t instanceof String) {
                    String value = cell.getStringCellValue();
                    list.add((T) value);
                } else {
                    Double value = 0d;
                    try { // 若获取的数据无法转换为Double则设置为 0
                        value = Double.parseDouble(cell.getStringCellValue());
                    } catch (Exception e) {
                        value = cell.getNumericCellValue();
                    }
                    list.add((T) value);
                }
            }
        }

        return list;
    }
}
